package com.shujia.dal

import com.shujia.utils.SparkTool
import org.apache.spark.sql.SparkSession

object DalCityTouristMskWideDay extends SparkTool {
  override def run(spark: SparkSession): Unit = {

    spark.sql(
      s"""
         |insert overwrite table dal.dal_city_tourist_msk_wide_d partition(day_id='$day_id')
         |select
         |/*+broadcast(c,d) */
         |a.mdn,
         |c.county_name as source_county_name,
         |c.city_name as source_city_name,
         |c.prov_name as source_province_name,
         |d.city_name as d_city_name,
         |case when a.d_stay_time >= 3 and a.d_stay_time < 6 then "[3-6)"
         |when a.d_stay_time >= 6 and a.d_stay_time < 9 then "[6-9)"
         |when a.d_stay_time >= 9 and a.d_stay_time < 12 then "[9-12)"
         |when a.d_stay_time >= 12 and a.d_stay_time < 15 then "[12-15)"
         |else '[15~' end  as d_stay_time,
         |case when a.d_max_distance >= 10   and a.d_max_distance<50 then  '[10-50)'
         |when a.d_max_distance >= 50   and a.d_max_distance<80 then  '[50-80)'
         |when a.d_max_distance >= 80   and a.d_max_distance<120 then '[80-120)'
         |when a.d_max_distance >= 120  and a.d_max_distance<200 then '[120-200)'
         |when a.d_max_distance >= 200  and a.d_max_distance<400 then '[200-400)'
         |when a.d_max_distance >= 400  and a.d_max_distance<800 then '[400-800)'
         |else '[800~' end  as d_distance,
         |case when b.gender ='1' then '男'
         |else '女' end as gender,
         |case when b.age > 0  and b.age <20 then '[0-20)'
         |when b.age > 20 and b.age <25 then '[20-25)'
         |when b.age > 25 and b.age <30 then '[25-30)'
         |when b.age > 30 and b.age <35 then '[30-35)'
         |when b.age > 35 and b.age <40 then '[35-40)'
         |when b.age > 40 and b.age <45 then '[40-45)'
         |when b.age > 45 and b.age <50 then '[45-50)'
         |when b.age > 50 and b.age <55 then '[50-55)'
         |when b.age > 55 and b.age <60 then '[55-60)'
         |when b.age > 60 and b.age <65 then '[60-65)'
         |else '[60~' end as age,
         |b.number_attr,
         |trmnl_brand,
         |trmnl_price,
         |packg,
         |conpot
         |from
         |(select * from  dws.dws_city_tourist_msk_d where day_id='$day_id') as a
         |join
         |(select * from  dim.dim_usertag_msk_d where day_id='$day_id') as b
         |on a.mdn=b.mdn
         |join
         |dim.dim_admincode as c
         |on a.source_county_id=c.county_id
         |join
         |(select distinct city_id,city_name from dim.dim_admincode) as d
         |on a.d_city_id=d.city_id
         |
         |
         |
         |
      """.stripMargin)

  }
}
